using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Deepcode.Flux.Core.Data;

namespace Deepcode.Flux.Core.Systems.CMS
{
	/// <summary>
	/// Primary class for retrieving content from the content repository
	/// </summary>
	public class ContentQuery : BaseManager
	{
		#region Member fields
		// The current sort order
		protected ContentQuerySortOrder _SortOrder = ContentQuerySortOrder.NONE;
		// The current sort direction
		protected DataSortDirection _SortDirection = DataSortDirection.ASCENDING;
		// For sortOrder = XMLField - the XML XPath field to sort by
		protected string _SortFieldXML = "";
		// For sortOrder = XMLField - the type statement to case the field to
		protected string _SortFieldCast = "";
		// The number of rows to return (-1 = unlimited)
		protected int _LimitRows = -1;
		// Holds the explicitly set IsStaging value
		protected bool _IsStaging = false;
		// Determines if IsStaging was explicitly set
		protected bool _IsStagingSet = false;
		// Holds the explisitly set IsStagingIgnoreDates value
		protected bool _IsStagingIgnoreDates = false;
		// Determines if the IsStagingIgnoreDates was explicitly set
		protected bool _IsStagingIgnoreDatesSet = false;
		// Stores the root matching node for content queries
		protected ContentQueryCriteria _BaseMatch = new ContentQueryCriteria();
		#endregion
		#region Public properties
		/// <summary>
		/// Get/set the order to return results in
		/// </summary>
		public ContentQuerySortOrder SortOrder
		{
			get
			{
				return _SortOrder;
			}
			set
			{
				_SortOrder = value;
			}
		}
		/// <summary>
		/// Get/Set the sort direction to return results in
		/// </summary>
		public DataSortDirection SortDirection
		{
			get
			{
				return _SortDirection;
			}
			set
			{
				_SortDirection = value;
			}
		}
		/// <summary>
		/// For SortOrder = XMLField, get/set the XML XPath field
		/// to sort the results by.
		/// </summary>
		public string SortFieldXML
		{
			get
			{
				return _SortFieldXML;
			}
			set
			{
				_SortFieldXML = value;
			}
		}
		/// <summary>
		/// When sorting by XML Field, this property gets/sets the
		/// SQL cast parameter for the field. Eg: nvarchar(200), int etc.
		/// </summary>
		public string SortFieldCast
		{
			get
			{
				return _SortFieldCast;
			}
			set
			{
				_SortFieldCast = value;
			}
		}
		/// <summary>
		/// Get/Set the limit on the number of rows the query should return
		/// (-1 is unlimited)
		/// </summary>
		public int LimitRows
		{
			get
			{
				return _LimitRows;
			}
			set
			{
				_LimitRows = value;
			}
		}
		/// <summary>
		/// Gets the root critria match node to build your query
		/// </summary>
		public ContentQueryCriteria BaseMatch
		{
			get
			{
				return this._BaseMatch;
			}
		}
		/// <summary>
		/// Property determining if the content returned should be staged content
		/// (ie: Latest version) or live content (ie: Current published). By default
		/// this will interrogate the session variable [StagingServer]. If that is found
		/// to exist and be of type bool, it is returned. You can override this behaviour 
		/// by explicitly setting IsStaging=[true/false], and the session variable will 
		/// be ignored.
		/// </summary>
		public bool IsStaging
		{
			get
			{
				// If we've explicitly set staging value, return that
				if( _IsStagingSet ) return _IsStaging;

				// Otherwise, use the HttpContext and session object
				System.Web.HttpContext c = System.Web.HttpContext.Current;
				if( c == null ) return false;
				if( c.Session == null ) return false;
				object o = c.Session["StagingServer"];
				if( o == null ) return false;
				if( !(o is bool) ) return false;
				return ((bool) o);
			}
			set
			{
				_IsStaging = value;
				_IsStagingSet = true;
			}
		}
		/// <summary>
		/// Property determining if the content returned should ignore embargo dates
		/// or not. Normally when querying for content, the embargo dates are checked
		/// against the current date to ensure content is visible. This can be overridden
		/// by setting this to property explicitly. By default this property interrogates
		/// the session variable [StagingServerIgnoreDates]. If that is found to exist and be
		/// bool, it is returned. This behaviour can be overridden explicitly by setting this
		/// property directly - the session will then be ignored.
		/// </summary>
		public bool IsStagingIgnoreDates
		{
			get
			{
				// See if we've setup to ignore dates explicitly
				if( _IsStagingIgnoreDatesSet ) return _IsStagingIgnoreDates;

				// Otherwise use the HttpContext and session object
				System.Web.HttpContext c = System.Web.HttpContext.Current;
				if( c == null ) return false;
				if( c.Session == null ) return false;
				object o = c.Session["StagingServerIgnoreDates"];
				if( o == null ) return false;
				if( !(o is bool) ) return false;
				return ((bool) o);
			}
			set
			{
				_IsStagingIgnoreDates = value;
				_IsStagingIgnoreDatesSet = true;
			}
		}
		#endregion
		#region SQL Builder
		/// <summary>
		/// Builds the SQL necessary to find the content objects
		/// </summary>
		protected void BuildSQL( ref string sql, ref List<SqlParameter> parameters )
		{
			string RowLimits = "";
			if( this.LimitRows > 0 ) RowLimits = String.Format("TOP {0}", this.LimitRows);
			string JoinField = "";
			if( IsStaging ) JoinField = "c.FK_VersionV";
			else JoinField = "c.FK_LiveV";

			string FieldList = GetFieldList();
			string FromTable = "FROM Content c INNER JOIN ContentV v ON v.PK_ID=" + JoinField;
			List<String> ListExtraJoins = new List<string>();
			string ExtraJoins = "";
			string CrossApply = "CROSS APPLY v.XMLData.nodes('*') as XMLTABLE(ITEMS)";
			string WhereSyntax = "WHERE";
			string WhereClauses = GetStandardWheres();
			string CriteriaWhereClauses = BaseMatch.Render( ref parameters, ref ListExtraJoins );
			if (CriteriaWhereClauses != String.Empty)
			{
				if (WhereClauses != String.Empty) WhereClauses += " AND ";
				WhereClauses += CriteriaWhereClauses;
			}

			if( WhereClauses == String.Empty ) WhereSyntax = "";

			foreach( string j in ListExtraJoins )
				ExtraJoins += " " + j;

			string OrderBy = GetOrderBy();
			
			// SELECT [FieldList] [FromTable] [!ExtraJoins] [CROSS APPLY] [Wheres] [OrderBy];
			sql = String.Format(
				"SELECT {0} {1} {2} {3} {4} {5} {6} {7}",
				RowLimits,
				FieldList,
				FromTable,
				ExtraJoins,
				CrossApply,
				WhereSyntax,
				WhereClauses,
				OrderBy);
		}
		protected string GetStandardWheres()
		{
			string where = "";
			if( IsStagingIgnoreDates ) return "";
			where += "((HasEmbargoStart=0) OR ((HasEmbargoStart=1) AND (PublishStartDT<GETDATE())))";
			where += " AND ";
			where += "((HasEmbargoEnd=0) OR ((HasEmbargoEnd=1) AND (PublishUntilDT>GETDATE())))";
			return where;
		}
		/// <summary>
		/// Returns the standard fields for the SELECT statement
		/// </summary>
		/// <returns></returns>
		protected string GetFieldList()
		{
			StringBuilder s = new StringBuilder();
			s.Append("c.PK_ID, c.FK_STypeID, c.FK_ParentID, c.Rank, c.NodeTitle, c.NodeName, c.NodePath,");
			s.Append("c.CreatedDT, c.FK_CreatedBy, c.LastModifiedDT, c.FK_LastModifiedBy, c.WorkflowModeID,");
			s.Append("c.WorkflowStatus, c.FK_CheckedOutTo, c.CheckedOutDT, c.CurrentVersionNumber,");
			s.Append("c.HasEmbargoStart, c.PublishStartDT, c.HasEmbargoEnd, c.PublishUntilDT,");
			s.Append("c.ExpiryActionID, c.ExpiryActionDataID, c.HasOwnSecurity,");
			s.Append("v.FK_CheckedInBy, v.CheckedInDT, v.VersionNumber, v.VersionNotes, v.XMLData");
			return s.ToString();
		}
		/// <summary>
		/// Determines the appropriate ORDER BY clause
		/// </summary>
		/// <returns></returns>
		protected string GetOrderBy()
		{
			string OrderBy = "";
			if (SortOrder != ContentQuerySortOrder.NONE)
			{
				if (SortOrder == ContentQuerySortOrder.XMLFIELD
					&& (SortFieldXML == "" || SortFieldCast == ""))
					throw new Exception("Cannot query against XMLFIELD without SortFieldXML and SortFieldCast properties");

				switch (SortOrder)
				{
					case ContentQuerySortOrder.RANK:
						OrderBy = "c.Rank";
						break;
					case ContentQuerySortOrder.NODETITLE:
						OrderBy = "c.NodeTitle";
						break;
					case ContentQuerySortOrder.NODENAME:
						OrderBy = "c.NodeName";
						break;
					case ContentQuerySortOrder.CREATED:
						OrderBy = "c.CreatedDT";
						break;
					case ContentQuerySortOrder.MODIFIED:
						OrderBy = "c.LastModifiedDT";
						break;
					case ContentQuerySortOrder.XMLFIELD:
						OrderBy = String.Format(
							"XMLTABLE.ITEMS.query('.').value('(/xml/{0})[1]', '{1}')",
							SortFieldXML,
							SortFieldCast);
						break;
				}

				if (OrderBy != "")
				{
					OrderBy = "ORDER BY " + OrderBy;
					if( SortDirection == DataSortDirection.DESCENDING ) OrderBy += " DESC";
				}
			}

			return OrderBy;
		}
		#endregion
		#region Public Methods
		/// <summary>
		/// Gets all content objects matching the criteria specified in the query object
		/// </summary>
		/// <returns></returns>
		public ContentObject[] GetMatching()
		{
			// Build SQL and get parameters
			List<SqlParameter> Parameters = new List<SqlParameter>();
			string sql = "";
			BuildSQL( ref sql, ref Parameters);

			// Connect
			DataTable results = new DataTable();
			EstablishConnection();
			// Create adapter and add the parameters
			SqlDataAdapter da = this.CreateDataAdapter( sql );
			da.SelectCommand.CommandType = CommandType.Text;
			foreach( SqlParameter p in Parameters )
				da.SelectCommand.Parameters.Add( p );
			// Run query and get results
			da.Fill(results);
			ReleaseConnection();

			// Load results
			List<ContentObject> list = new List<ContentObject>();
			foreach( DataRow r in results.Rows )
				list.Add( new ContentObject(r));

			return list.ToArray();
		}
		/// <summary>
		/// Trace function to determine what the SQL Builder function is returning
		/// </summary>
		/// <returns></returns>
		public string TraceSQL()
		{
			List <SqlParameter> P = new List<SqlParameter>();
			string sql = "";

			BuildSQL( ref sql, ref P );
			return sql;
		}
		#endregion
		#region Quick Helper functions
		/// <summary>
		/// Get a single content object by it's ID
		/// </summary>
		/// <param name="contentId"></param>
		/// <returns></returns>
		public static ContentObject GetByID(int contentId)
		{
			ContentQuery query = new ContentQuery();
			query.BaseMatch.AND(new StaticFieldMatch(ContentStaticField.ID, ContentQueryOperator.EQ, contentId));
			ContentObject [] objs = query.GetMatching();
			if( objs.Length > 0 ) return objs[0];
			return null;
		}
		/// <summary>
		/// Returns a single content object based on the content
		/// path specified.
		/// </summary>
		/// <param name="contentPath"></param>
		/// <returns></returns>
		public static ContentObject GetByPath(string contentPath)
		{
			string cpwork = contentPath.Replace("\\", "/");
			string [] pathSplit = cpwork.Split('/');
			List<string> pieces = new List<string>();

			foreach( string item in pathSplit )
				if( item.Trim() != String.Empty ) pieces.Add( item );

			string Path = "/";
			string NodeName = "";

			if( pieces.Count > 1 )	
				for( int x=0; x<pieces.Count-1; x++ )
					Path += pieces[x] + "/";
			if( pieces.Count > 0 )
				NodeName = pieces[pieces.Count-1];
			
			ContentQuery query = new ContentQuery();
			query.BaseMatch.AND( new StaticFieldMatch(ContentStaticField.Path, ContentQueryOperator.EQ, Path));
			query.BaseMatch.AND( new StaticFieldMatch(ContentStaticField.Name, ContentQueryOperator.EQ, NodeName));
			ContentObject [] objs = query.GetMatching();

			if( objs.Length > 0 ) return objs[0];
			return null;
		}
		/// <summary>
		/// Returns the content objects that are within a particular parent
		/// </summary>
		/// <param name="parentContentId"></param>
		/// <returns></returns>
		public static ContentObject [] GetForParentID(int parentContentId)
		{
			ContentQuery query = new ContentQuery();
			query.BaseMatch.AND( new StaticFieldMatch( ContentStaticField.ParentID, ContentQueryOperator.EQ, parentContentId ));
			query.SortOrder = ContentQuerySortOrder.RANK;
			query.SortDirection = DataSortDirection.ASCENDING;
			return query.GetMatching();
		}
		#endregion
	}
}
